package com.huarui.permission.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.ListUtils;
import org.ewaf.framework.model.IDataObject;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.orm.ibatis.SqlMapClientTemplate;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import com.huarui.companypackage.model.Service;
import com.huarui.permission.dao.PermissionDao;
import com.huarui.permission.dataobject.Permission;
import com.huarui.permission.dataobject.Role;
import com.huarui.permission.dataobject.RoleTemplate;
import com.huarui.permission.service.PermissionService;
import com.mysql.jdbc.Statement;

public class PermissionDaoImpl implements PermissionDao {

	public JdbcTemplate jdbcTemplate;

	private SqlMapClientTemplate sqlMapClientTemplate;

	public SqlMapClientTemplate getSqlMapClientTemplate() {
		return sqlMapClientTemplate;
	}

	public void setSqlMapClientTemplate(SqlMapClientTemplate sqlMapClientTemplate) {
		this.sqlMapClientTemplate = sqlMapClientTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getRoleIds(Integer employeeId) {

		return (List<Integer>) jdbcTemplate.queryForList("select roleId from roleEmployee where employeeId=?", new Object[] { employeeId }, Integer.class);
	}

	/*
	 * add by sunpeng. to get perminssion ids.
	 * @see com.huarui.permission.dao.PermissionDao#getPermissionIds(java.lang.Integer)
	 */
	@SuppressWarnings("unchecked")
	public List<Integer> getPermissionIds(Integer employeeId) {
		return (List<Integer>) jdbcTemplate
				.queryForList(
						"select permissionId from rolePermission where roleId in (select roleId from roleEmployee where employeeId=?) union all select permissionId from permissionService where serviceId in (select id from service where target=?)",
						new Object[] { employeeId, Service.SERVICE_TARGET_COMMON_USER }, Integer.class);
	}

	@SuppressWarnings("unchecked")
	@Override
	public int hasPermission(Integer permissionId, List<Integer> roleIds) {

		List<Integer> rIds = jdbcTemplate.queryForList("select roleId from rolePermission where permissionId=?", new Object[] { permissionId }, Integer.class);
		return ListUtils.intersection(roleIds, rIds).isEmpty() ? PermissionService.RESULT_NO_PERMISSION : PermissionService.RESULT_HAS_PERMISSION;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<RoleTemplate> getRoleTemplates(Integer targetType,Integer targetValue) {

		final String sql="select rt.* from roleTemplate rt, roleTemplateTarget rtt " +
		" where rtt.targetType=? and rtt.targetValue=? and rt.id=rtt.roleTemplateId";
		return jdbcTemplate.query(sql, new Object[] {targetType,targetValue}, new RowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

				RoleTemplate roleTemplate = new RoleTemplate();

				roleTemplate.setId(rs.getInt("id"));
				roleTemplate.setEditable(rs.getInt("editable"));
				roleTemplate.setIsAdministartor(rs.getInt("isAdministrator"));
				roleTemplate.setRoleName(rs.getString("roleName"));
				
				return roleTemplate;
			}
		});

	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> getDefaultPermissionIds(Integer roleTemplateId) {

		return jdbcTemplate.queryForList("select permissionId from roleTemplatePermission where roleTemplateId=?", new Object[] { roleTemplateId }, Integer.class);
	}

	@Override
	public Role createRole(final RoleTemplate roleTemplate, Integer companyId) {

		final Role role = new Role();
		role.setCompanyId(companyId);
		role.setEditable(roleTemplate.getEditable());
		role.setIsAdministrator(roleTemplate.getIsAdministartor());
		role.setName(roleTemplate.getRoleName());

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				PreparedStatement ps = con.prepareStatement("INSERT INTO role ( name , companyId , isAdministrator, editable ,roleTemplateId )" + "  VALUES (?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);

				ps.setString(1, role.getName());
				ps.setInt(2, role.getCompanyId());
				ps.setInt(3, role.getIsAdministrator());
				ps.setInt(4, role.getEditable());
				ps.setInt(5, roleTemplate.getId());

				return ps;
			}
		}, keyHolder);

		role.setId(keyHolder.getKey().intValue());

		return role;
	}

	@Override
	public void perparePermissions(final RoleTemplate roleTemplate, final Role role) {

		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				final String str ="insert into rolePermission (roleId, permissionId)" 
						+ " select " + role.getId().toString() + ", permissionId from roleTemplatePermission"
						+ " where  roleTemplateId=? and permissionId in ("
							+" select distinct permissionId from permissionService where serviceId in "
								+" (select serviceId from companyService where companyId=? ))";
				PreparedStatement ps = con.prepareStatement(str);;
				ps.setInt(1, roleTemplate.getId());
				ps.setInt(2, role.getCompanyId());

				return ps;
			}
		});

	}

	private TransactionTemplate transactionTemplate;

	public TransactionTemplate getTransactionTemplate() {
		return transactionTemplate;
	}

	public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
		this.transactionTemplate = transactionTemplate;
	}

	public boolean deleteAllRolesAndPermission(final Integer companyId) {

		transactionTemplate.execute(new TransactionCallback() {

			@Override
			public Object doInTransaction(TransactionStatus status) {
				jdbcTemplate.update("delete from rolePermission where roleId in ( select id from role where companyId=? )", new Object[] { companyId });
				jdbcTemplate.update("delete from role where companyId=?", new Object[] { companyId });

				return null;

			}
		});

		return true;

	}

	@Override
	public List<Role> createRoles(final List<RoleTemplate> roleTemplates, final Integer companyId) {

		/*
		 * KeyHolder keyHolder = new GeneratedKeyHolder();
		 * jdbcTemplate.update(new PreparedStatementCreator() {
		 * 
		 * @Override public PreparedStatement createPreparedStatement(Connection
		 * con) throws SQLException {
		 * 
		 * PreparedStatement ps = con.prepareStatement(
		 * "INSERT INTO role ( name , companyId , isAdministrator, deletable)" +
		 * "  VALUES (?,?,?,?)", Statement.RETURN_GENERATED_KEYS); for
		 * (RoleTemplate roleTemplate : roleTemplates) {
		 * 
		 * 
		 * ps.setString(1, roleTemplate.getRoleName()); ps.setInt(2, companyId);
		 * ps.setInt(3, roleTemplate.getIsAdministartor()); ps.setInt(4,
		 * roleTemplate.getDeletable());
		 * 
		 * ps.addBatch(); }
		 * 
		 * return ps; } }, keyHolder);
		 * 
		 * final List<Role> roles = new ArrayList<Role>();
		 * 
		 * 
		 * for(int i=0; i<roleTemplates.size();i++){ Role role =new Role();
		 * role.setCompanyId(roleTemplates.get(i).getCompanyType()); role.setId(
		 * ((Number) keyHolder.getKeyList().get(i)).intValue());
		 * role.setIsAdministartor(roleTemplates.get(i).getIsAdministartor());
		 * role.setDeletable(roleTemplates.get(i).getDeletable());
		 * 
		 * roles.add(role); }
		 */

		final List<Role> roles = new ArrayList<Role>();
		for (RoleTemplate roleTemplate : roleTemplates) {
			Role role = createRole(roleTemplate, companyId);
			roles.add(role);
		}

		/*
		 * final List<Role> roles = new ArrayList<Role>();
		 * 
		 * 
		 * sqlMapClientTemplate.execute(new SqlMapClientCallback() {
		 * 
		 * 
		 * 
		 * @Override public Object doInSqlMapClient(SqlMapExecutor executor)
		 * throws SQLException {
		 * 
		 * executor.startBatch();
		 * 
		 * for(int i=0; i<roleTemplates.size();i++){ Role role =new Role();
		 * role.setCompanyId(companyId);
		 * role.setName(roleTemplates.get(i).getRoleName());
		 * 
		 * role.setIsAdministrator(roleTemplates.get(i).getIsAdministartor());
		 * role.setDeletable(roleTemplates.get(i).getDeletable());
		 * 
		 * 
		 * 
		 * executor.insert(namespace + ".createRole", role); roles.add(role); }
		 * 
		 * executor.executeBatch();
		 * 
		 * return null; } });
		 */

		return roles;
	}

	private String namespace = "permission";

	public Role createRole(Role role) {

		sqlMapClientTemplate.insert(namespace + ".createRole", role);
		return role;

	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Role> grantCompanyAllRole(Integer companyId) {

		final String sql="select * from role where companyId=?" ;
		
		return jdbcTemplate.query(sql, new Object[] {companyId}, new RowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

				Role role = new Role();

				role.setId(rs.getInt("id"));
				role.setEditable(rs.getInt("editable"));
				role.setIsAdministrator(rs.getInt("isAdministrator"));
				role.setName(rs.getString("name"));
				role.setRoleTemplateId(rs.getInt("roleTemplateId"));
				role.setCompanyId(rs.getInt("companyId"));
				return role;
			}
		});
	}

	@Override
	public void addCompanyAdminRole(final Integer employeeId, final Integer companyId) {
		
		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				final String str ="insert into roleemployee (employeeId,roleId)" +
						" select "+employeeId.toString()+",id from role where companyId=? and IsAdministrator=1";
				PreparedStatement ps = con.prepareStatement(str);;
				ps.setInt(1,companyId);

				return ps;
			}
		});
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Permission> getAllPermissions() {
		List<Permission> list=this.getSqlMapClientTemplate().queryForList(namespace+".getAllPermissions");
		return list;
	}

	@Override
	public int deleteByCondition(Map map) {
		return getSqlMapClientTemplate().delete(namespace + ".deleteByCondition", map);
		
	}

	public void create(IDataObject data) {
		getSqlMapClientTemplate().insert(namespace + ".create", data);
	}


	@Override
	public Permission getById(Map<String,Object> map) {
		return (Permission) getSqlMapClientTemplate().queryForObject(namespace + ".getById", map);
	}


	@Override
	public boolean update(IDataObject data) {
		return getSqlMapClientTemplate().update(namespace + ".update", data) == 1;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Permission> getPermissionListByCondition(Map map) {
		return getSqlMapClientTemplate().queryForList(namespace+".getPermissionListByCondition", map);
	}

	@Override
	public void perparePermissions(final Role role,final Integer donateType) {
		jdbcTemplate.update(new PreparedStatementCreator() {

			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {

				final String str ="insert into rolePermission (roleId, permissionId)" 
						+ " select " + role.getId().toString() + ", permissionId from roleTemplatePermission"
						+ " where  roleTemplateId=? and permissionId in ("
							+" select permissionId from permissionService where serviceId in "
							+" (select s.id "
					  		+" from service s , packageService ps"
					  		+" where s.id=ps.serviceId and ps.packageId in "
					  		+"(select id from package where isGift=1 and giftCondition=? and gmtBegin<now() and gmtEnd>now())))";
				PreparedStatement ps = con.prepareStatement(str);;
				ps.setInt(1, role.getRoleTemplateId());
				ps.setInt(2, donateType);
				return ps;
			}
		});
	}

/*	@Override
	public Action findAction(String action, String method) {
		return (Action) jdbcTemplate.queryForObject("select * from action where name=? and method=?", new Object[] { action, method }, Action.class);
	}*/
}
